﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using HHL_Acc.HHL_DaoAcc.DataBase;
using HHL_Acc.HHL_EntityAcc;

namespace HHL_Acc.HHL_DaoAcc.DataObject
{
    public class SmiImportsDao : CBaseDao
    {
        public DataTable GetAllSmiImports()
        {
            String sql =
                @"Select ROW_NUMBER() OVER (ORDER BY im.ImpID) as Idx,im.Imp_Number,im.Imp_Date, im.Issue_Year, SUM(mid.Qty)as Qty,sim.ImpName, Deliver, (em.LastName + em.FirstName) as Receiver, im.Notes 
        from Smi_Imports im INNER JOIN Gen_Logins lo ON im.Login = lo.Login and lo.Disabled =0
        INNER JOIN Gen_Employees em ON lo.Empid = em.Empid AND em.Disabled = 0
        INNER JOIN Sys_ImpMethods sim ON sim.ImpMethod = im.ImpMethod AND sim.Disabled = 0
        INNER JOIN Smi_Import_Details mid ON im.ImpID = mid.ImpID 
        GROUP BY im.ImpID,im.Imp_Number,im.Imp_Date, im.Issue_Year,sim.ImpName,Deliver,em.LastName,em.FirstName,im.Notes";
            DataSet ds = ExcuteSql(sql);
            return ds.Tables[0];
        }

        public IList<SmiImports> GetAllSmiImportsL()
        {
            String sql =
                @"Select ROW_NUMBER() OVER (ORDER BY im.ImpID) as Idx,im.Imp_Number as ImpNumber,im.Imp_Date as ImpDate, im.Issue_Year as IssueYear, SUM(mid.Qty)as Qty,sim.ImpName as ImpMethodName, Deliver, (em.LastName +' ' + em.FirstName) as ImpName, im.Notes , im.ImpId ,im.Status
        from Smi_Imports im INNER JOIN Gen_Logins lo ON im.Login = lo.Login and lo.Disabled =0
        INNER JOIN Gen_Employees em ON lo.Empid = em.Empid AND em.Disabled = 0
        INNER JOIN Sys_ImpMethods sim ON sim.ImpMethod = im.ImpMethod AND sim.Disabled = 0
        INNER JOIN Smi_Import_Details mid ON im.ImpID = mid.ImpID 
        GROUP BY im.ImpID,im.Imp_Number,im.Imp_Date, im.Issue_Year,sim.ImpName,Deliver,em.LastName,em.FirstName,im.Notes,im.Status";
            DataSet ds = ExcuteSql(sql);
            if (ds != null)
            {
                return CDb.MapList<SmiImports>(ds.Tables[0]);
            }
            return null;

            //return Db.MapList<SmiImports>(ds.Tables[0]);
        }

        public DataTable GetSmiImportsWithCondition(int? importNumber, int? Year, DateTime dateFrom, DateTime dateTo)
        {
            String sql =
                @"Select ROW_NUMBER() OVER (ORDER BY im.ImpID) as Idx,im.Imp_Number,im.Imp_Date, im.Issue_Year, SUM(mid.Qty)as Qty,sim.ImpName, Deliver, (em.LastName + em.FirstName) as Receiver, im.Notes 
        from Smi_Imports im INNER JOIN Gen_Logins lo ON im.Login = lo.Login and lo.Disabled =0
        INNER JOIN Gen_Employees em ON lo.Empid = em.Empid AND em.Disabled = 0
        INNER JOIN Sys_ImpMethods sim ON sim.ImpMethod = im.ImpMethod AND sim.Disabled = 0
        INNER JOIN Smi_Import_Details mid ON im.ImpID = mid.ImpID ";
            sql += " WHERE ";

            // importNumber
            sql += importNumber == null ? "" : "im.Imp_Number = " + importNumber;

            // Year
            if (Year != null)
            {
                sql += importNumber == null ? " " : " AND ";
                sql += "im.Issue_Year = " + Year;
            }
            if (dateFrom != DateTime.MinValue && dateTo != DateTime.MinValue)
            {
                if (Year != null || importNumber != null)
                {
                    sql += " AND ";
                }
                sql += "im.Imp_Date BETWEEN '" + dateFrom.ToShortDateString();
                sql += "' AND '" + dateTo.ToShortDateString() + "'";
            }
            sql +=
                " GROUP BY im.ImpID,im.Imp_Number,im.Imp_Date, im.Issue_Year,sim.ImpName,Deliver,em.LastName,em.FirstName,im.Notes";
            DataSet ds = ExcuteSql(sql);

            if (ds != null)
            {
                return ds.Tables[0];
            }
            return null;
        }

        public SmiImports GetAllSmiImportsById(String importId)
        {
            const string sql = "SELECT * from Smi_Imports where impId=@ImpID";
            DataSet ds = ExcuteSql(sql, CommandType.Text,
                                   new SqlParameter("@ImpID", importId));
            if (ds != null && ds.Tables[0] != null)
            {
                SmiImports smiImports = CDb.Map<SmiImports>(ds.Tables[0]);

                return smiImports;
            }
            return null;
        }

        public int InsertSmiImports(SmiImports imports)
        {
            const String sql =
                "INSERT Smi_Imports(ImpID,Login,Deliver,ImpMethod,Imp_Date,Imp_Hour,ShiftID,Issue_Year,Imp_Number,Status,LastModified,Notes) VALUES (@ImpID,@Login,@Deliver,@ImpMethod,@Imp_Date,@Imp_Hour,@ShiftID,@Issue_Year,@Imp_Number,@Status,@LastModified,@Notes)";
            SqlParameter[] paramsSql = new SqlParameter[12];
            paramsSql[0] = new SqlParameter("@ImpID", imports.ImpId);
            paramsSql[1] = new SqlParameter("@Login", imports.Login);
            paramsSql[2] = new SqlParameter("@Deliver", imports.Deliver);
            paramsSql[3] = new SqlParameter("@ImpMethod", imports.ImpMethod);
            paramsSql[4] = new SqlParameter("@Imp_Date", imports.ImpDate);
            paramsSql[5] = new SqlParameter("@Imp_Hour", imports.ImpHour);
            paramsSql[6] = new SqlParameter("@ShiftID", imports.ShiftId);
            paramsSql[7] = new SqlParameter("@Issue_Year", imports.IssueYear);
            paramsSql[8] = new SqlParameter("@Status", imports.Status);
            paramsSql[9] = new SqlParameter("@Imp_Number", imports.ImpNumber);
            paramsSql[10] = new SqlParameter("@LastModified", imports.LastModified);
            paramsSql[11] = new SqlParameter("@Notes", imports.Notes);

            return ExcuteNoneQuery(sql, paramsSql);
        }


        public int UpdateSmiImports(SmiImports imports)
        {
            const String sql =
                @"Update Smi_Imports set Login = @Login ,Deliver = @Deliver,ImpMethod = @ImpMethod,Issue_Year = @Issue_Year,
Imp_Number = @Imp_Number,Status = @Status,LastModified = @LastModified,Notes = @Notes WHERE ImpID =  @ImpID";
            SqlParameter[] paramsSql = new SqlParameter[9];
            paramsSql[0] = new SqlParameter("@ImpID", imports.ImpId);
            paramsSql[1] = new SqlParameter("@Login", imports.Login);
            paramsSql[2] = new SqlParameter("@Deliver", imports.ImpId);
            paramsSql[3] = new SqlParameter("@ImpMethod", imports.ImpMethod);
            paramsSql[4] = new SqlParameter("@Issue_Year", imports.IssueYear);
            paramsSql[5] = new SqlParameter("@Status", imports.Status);
            paramsSql[6] = new SqlParameter("@Imp_Number", imports.ImpNumber);
            paramsSql[7] = new SqlParameter("@LastModified", imports.LastModified);
            paramsSql[8] = new SqlParameter("@Notes", imports.Notes);

            return ExcuteNoneQuery(sql, paramsSql);
        }
    }
}